0. Theory - interview

1. difference between int and big int

• difference lies in the size of each int is of 4 bytes which is from -2,147,483,648 to 2,147,483,647 whereas the big int is of 8 bytes which is from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
• note that BIGINT uses more storage and may have performance implications.


2. Difference between char and varchar

The main difference between CHAR and VARCHAR in SQL is how they store and manage string length:

• CHAR(n) is fixed-length: it always stores exactly n characters, padding with spaces if the input is shorter.
• VARCHAR(n) is variable-length: it stores only the number of characters entered, up to n, with no padding.
  Example:

-- CHAR(5)
INSERT INTO test_char VALUES ('Hi');  
-- Stored as: 'Hi   ' (with 3 trailing spaces)

-- VARCHAR(5)
INSERT INTO test_varchar VALUES ('Hi');  
-- Stored as: 'Hi' (no extra spaces)

Use CHAR when values are always the same length (e.g., fixed codes like 'A123'), and VARCHAR when lengths vary (e.g., names, emails).

Yes, CHAR can be slightly faster than VARCHAR — but only in very specific scenarios. Fixed-length makes memory allocation predictable and makes indexing faster

3. what are the types of sql commands

1. DQL - Data query language Used to retrieve data from databases. (SELECT)
2. DDL - data definition language Used to create, alter, and delete database objects like tables, indexes, etc. (CREATE, DROP, ALTER, RENAME, TRUNCATE)
3. DML - data manipulation lang Used to modify the database. (INSERT,UPDATE, DELETE)
4. DCL - data control language Used to grant & revoke permissions. (GRANT,REVOKE)
5. TQL - Transaction control language Used to manage transactions. (COMMIT,ROLLBACK, START TRANSACTIONS, SAVEPOINT)

A transaction is a group of SQL operations that are executed as a single unit of work, ensuring data integrity.
Key properties (ACID):

• Atomicity: all or nothing
• Consistency: data remains valid
• Isolation: no interference from other transactions
• Durability: once committed, changes are permanent
  Example:

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT; -- or ROLLBACK;

If an error occurs, you can ROLLBACK to undo all operations.

3. primary key - foreign key 3. Keys

A foreign key is a column in one table that links to the primary key of another table.
It creates a relationship between two tables.

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE StudentCourse (
    student_id INT,
    course_id INT,
    FOREIGN KEY (student_id) REFERENCES Students(student_id)
);

Unique key

• primary key identifies each record uniquely in the table where there can be no null or double records also in a table there can be only one primary key
• wheras unique key is also a unique identfier of the column but the values can be zero and there can be multiple unique key columns

candidate key

a column or set of columns that can qualify as unique identifier
a table has multiple candidate key but only one is choosen as primary key

alternate key

a candidate key which is not choosen as primary key still unique and can be used

🔹 1. What are constraints in SQL? Name types.

Expected answer:
Constraints are rules applied on columns to enforce data integrity.

Types:

• PRIMARY KEY
• FOREIGN KEY
• NOT NULL
• UNIQUE
• CHECK
• DEFAULT

🔹 2. Create a table with all types of constraints.

Question:
Create a students table where:

• id is the primary key
• name cannot be null
• marks must be between 0 and 100
• email should be unique
• dept defaults to 'CSE'

Answer:

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    marks INT CHECK (marks >= 0 AND marks <= 100),
    email VARCHAR(100) UNIQUE,
    dept VARCHAR(20) DEFAULT 'CSE'
);

🔹 3. What's the difference between UNIQUE and PRIMARY KEY?

🔹 4. Can a table have multiple primary keys?

Correct answer: No. A table can only have one PRIMARY KEY, but it can consist of multiple columns (called a composite key).

CREATE TABLE attendance (
    student_id INT,
    date DATE,
    PRIMARY KEY (student_id, date)
);

🔹 5. What is the default constraint?

Sets a default value when no value is provided.

CREATE TABLE users (
    id INT PRIMARY KEY,
    status VARCHAR(10) DEFAULT 'active'
);

🔹 6. How to add a constraint after table creation?

-- Add NOT NULL
ALTER TABLE students
ALTER COLUMN name VARCHAR(50) NOT NULL;

-- Add CHECK
ALTER TABLE students
ADD CONSTRAINT chk_marks CHECK (marks >= 0 AND marks <= 100);

🔹 7. What is a foreign key? Give an example.

Links one table's column to another's primary key.

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

🔹 8. What happens if you try to insert duplicate in a UNIQUE column?

You get a constraint violation error — SQL Server won't allow it.

🔹 9. Can you disable constraints temporarily?

Yes, in SQL Server:

ALTER TABLE students NOCHECK CONSTRAINT ALL;

But this is not recommended unless you're doing controlled bulk operations.

🔹 10. What’s the order of constraint evaluation?

When inserting data, constraints are checked in this rough order:

• NOT NULL
• DEFAULT
• CHECK
• UNIQUE
• FOREIGN KEY
• PRIMARY KEY

This helps when debugging constraint errors.

🔶 1. Composite Keys vs Surrogate Keys

Question: What are the pros and cons of using composite primary keys over surrogate (auto-increment) keys in large databases?

What they look for:

• Understanding normalization
• How primary keys affect indexing and joins
• Tradeoffs between natural vs artificial keys

🔶 2. Multi-column Unique Constraint

Question: Create a table where email and phone can be duplicated separately, but not together.

Expected:

CREATE TABLE contacts (
    id INT PRIMARY KEY,
    email VARCHAR(100),
    phone VARCHAR(20),
    CONSTRAINT unique_email_phone UNIQUE (email, phone)
);

🔶 3. Complex CHECK Constraints

Question: Allow students to have marks only if their age is at least 18.

Expected:

CHECK (age >= 18 OR marks IS NULL)

They want to see if you can implement business logic using constraints.

🔶 4. Self-Referential Foreign Key

Question: Create a table employees where manager_id is a foreign key to employee_id in the same table.

Expected:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

Follow-up: What happens if a manager is deleted?

🔶 5. Cyclic or Cascading Deletes

Question: What happens if two tables have circular foreign keys with ON DELETE CASCADE?

They're checking if you understand referential cycles and danger of accidental mass deletions.

🔶 6. Add a constraint that a student's marks must be higher than all others in their dept

This can’t be done with a simple CHECK. You'd need:

• Triggers
• Procedural code (T-SQL)

This shows you understand limitations of constraints.

🔶 7. What’s the difference between UNIQUE constraint vs DISTINCT in a SELECT?

• UNIQUE affects data storage (prevents duplicates).
• DISTINCT only affects query results (hides duplicates in output).

🔶 8. Why shouldn't we use NULL in a PRIMARY KEY?

• Violates uniqueness requirement.
• Even if allowed syntactically (in some databases), it's logically incorrect because NULL ≠ NULL.

🔶 9. Enforcing ENUM-like values without ENUM keyword

How would you restrict a gender column to only 'M', 'F', 'O' in SQL Server?

Expected:

CHECK (gender IN ('M', 'F', 'O'))

🔶 10. Design Question: What constraints would you apply to a bank_accounts table?

This is open-ended. A good answer would include:

• account_id → PRIMARY KEY
• balance → CHECK (balance >= 0)
• account_type → CHECK (account_type IN (...))
• customer_id → FOREIGN KEY to customers
• Maybe a UNIQUE constraint on account_number